package com.itqf.dao.impl;

import com.itqf.dao.ProductDao;
import com.itqf.entity.Product;
import com.itqf.entity.Type;
import com.itqf.utils.DbUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ProductDaoImpl implements ProductDao {
    //调用Apache工具类
    private static QueryRunner queryRunner = new QueryRunner();

    @Override
    public long selectCountByTid(String tid) throws SQLException {
        //编写sql语句
        String sql = "select count(1) from product where t_id=?";
        Object result = queryRunner.query(DbUtils.getConnection(), sql, new ScalarHandler(), tid);
        Long total = (Long) result;
        return total;
    }

    @Override
    public List<Product> selectProductByPage(int page, int pageSize, String tid) throws SQLException {

        String sql = "select p_id as pid,t_id as tid,p_name as pname,p_time as ptime ," +
                "p_image as pimage,p_state as pstate ,p_info as pinfo ,p_price as pprice " +
                "from product where t_id = ? limit ?,?;";

        List<Product> list = queryRunner.query(DbUtils.getConnection(), sql, new BeanListHandler<Product>(Product.class),
                tid, (page - 1) * pageSize, pageSize);
        return list;
    }

    @Override
    public Product selectProductByPid(String pid) throws SQLException {
        String sql = "select p_id as pid,t_id as tid,p_name as pname,p_time as ptime ," +
                "p_image as pimage,p_state as pstate ,p_info as pinfo ,p_price as pprice " +
                "from product where p_id = ? ";
        Product product = queryRunner.query(DbUtils.getConnection(), sql, new BeanHandler<Product>(Product.class), pid);
        return product;
    }

    @Override
    public List<Product> selectAll() throws SQLException, InvocationTargetException, IllegalAccessException {

        String sql = "select p.p_id as pid,p.t_id as tid,p.p_name as pname,p.p_time as ptime,\n" +
                "p.p_image as pimage,p.p_state as pstate ,p.p_info as pinfo ,p.p_price as pprice,t.t_name as tname\n" +
                "from type t join product p on p.t_id = t.t_id ";

        List<Map<String, Object>> list = queryRunner.query(DbUtils.getConnection(), sql, new MapListHandler());

        List<Product> productList = new ArrayList<>();
        for (Map<String, Object> map : list) {
            //创建对象，以便映射
            Product product = new Product();
            Type type = new Type();

            //将集合中相关数据进行映射
            BeanUtils.populate(product, map);
            BeanUtils.populate(type, map);

            product.setType(type);

            productList.add(product);
        }
        return productList;
    }

    @Override
    public void addProduct(Product product) throws SQLException {
        String sql = "insert into product (t_id,p_name,p_time,p_image,p_state,p_info,p_price)\n" +
                "values(?,?,?,?,?,?,?);";
        //(int tid, String pname, Date ptime, String pimage, int pstate, String pinfo, BigDecimal pprice) {
        Object[] obj = {product.getTid(),product.getPname(),product.getPtime(),product.getPimage(),product.getPstate(),product.getPinfo(),product.getPprice()};
        queryRunner.update(DbUtils.getConnection(),sql,obj);
    }
}
